﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using cmsservice.Entity;

namespace cmsservice.DAL
{
    public class BlockRStockDAL
    {
        public List<BlockRStock> GetAll()
        {
            string sqlCmd = @"SELECT  SecuCode = A.f004v_10205 ,
                                    SecuName = C.SECUABBR ,
                                    BlockCode = B.f005v_10202 ,
                                    BlockName = B.f004v_10202 ,
                                    行业分类 = B.f002v_10202
                            FROM    tb_pub_10205 A
                                    INNER JOIN [SECURITY] AS C WITH ( NOLOCK ) ON A.f004v_10205 = C.SecuCode
                                    INNER JOIN tb_pub_10202 AS B WITH ( NOLOCK ) ON a.f002g_10205 = b.f001g_10202
                            WHERE   B.f002v_10202 = '二级行业'
                                    AND a.valid = '1'
                                    AND b.valid = 1
                                    AND C.SecuCategory = '001001'
                                    AND C.ListState IN ( '013001', '013004', '013005' )
                                    AND C.secumarket IN ( '012001', '012002', '012003', '012015' )  --- and   a.f004v_10205 ='000301'
                            ORDER BY f004v_10205";
            /*
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("Select SecuCode=A.f004v_10205,SecuName=C.SECUABBR,BlockCode=B.f005v_10202,BlockName=B.f004v_10202,行业分类=B.f002v_10202");
            sb.AppendLine("from tb_pub_10205 A inner join");
            sb.AppendLine("SECURITY C on A.f004v_10205=C.SecuCode");
            sb.AppendLine("inner join tb_pub_10202 B on a.f002g_10205=b.f001g_10202");
            sb.AppendLine("where B.f002v_10202 ='二级行业' and a.valid='1'and b.valid=1 and ");
            sb.AppendLine("C.SecuCategory='001001'AND C.ListState IN ('013001','013004','013005') and");
            sb.AppendLine("C.secumarket IN ('012001','012002','012003','012015')  --- and   a.f004v_10205 ='000301'");
            sb.AppendLine("order by f004v_10205");
             * */
            return SqlHelper.GetDataByColumnsLst<BlockRStock>(DBConn.NewDMSConnstr, sqlCmd, null);
        }
    }
}
